package database.paymain;

import com.sun.istack.internal.NotNull;
import database.CRUD;

import javax.swing.table.DefaultTableModel;
import java.sql.*;

public class tb_pay_main {//采购主表
    @NotNull
    private int Pid;//采购清单
    @NotNull
    private String Eid;//员工编号
    @NotNull
    private int Pcount;//采购数量
    @NotNull
    private float Ptotal;//采购总价
    @NotNull
    private String Pdate;//采购时间
    private String other;//备注
    tb_pay_main(int Pid, String Eid, int Pcount, float Ptotal, String Pdate, String other) {
        this.Pid = Pid;
        this.Eid = Eid;
        this.Pcount = Pcount;
        this.Ptotal = Ptotal;
        this.Pdate = Pdate;
        this.other = other;
    }
    public tb_pay_main() {}

    public void setPid(int pid) {
        Pid = pid;
    }
    public void setEid(String eid) {
        Eid = eid;
    }
    public void setPcount(int pcount) {
        Pcount = pcount;
    }
    public void setPtotal(float ptotal) {
        Ptotal = ptotal;
    }
    public void setPdate(String pdate) {
        Pdate = pdate;
    }
    public void setOther(String other) {
        this.other = other;
    }
    public int getPid() {
        return Pid;
    }
    public String getEid() {
        return Eid;
    }
    public int getPcount() {
        return Pcount;
    }
    public float getPtotal() {
        return Ptotal;
    }
    public String getPdate() {
        return Pdate;
    }
    public String getOther() {
        return other;
    }
    // 插入新采购明细
    public boolean insertPayMain(int Pid, String Eid, int Pcount, float Ptotal, String Pdate, String other) {
        String sql = "INSERT INTO tb_pay_main (Pid, Eid, Pcount, Ptotal, Pdate, other) "
                + "VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "123456");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置预编译语句的参数值
            pstmt.setInt(1, Pid);
            pstmt.setString(2, Eid);
            pstmt.setInt(3, Pcount);
            pstmt.setFloat(4, Ptotal);
            pstmt.setString(5, Pdate);
            pstmt.setString(6, other);

            // 执行更新操作
            int rowsAffected = pstmt.executeUpdate();

            if (rowsAffected > 0) {
                return true; // 插入成功
            } else {
                return false; // 插入失败
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 插入失败
        }
    }


    // 更新采购明细信息
    public boolean updatePayDetail(int Pid, String Eid, int Pcount, float Ptotal, String Pdate, String other) {
        String sql = "UPDATE tb_pay_main SET Eid = ?, Pcount = ?, Ptotal = ?, other = ? "
                + "WHERE Pid = ?";
        Object[] params = {Eid, Pcount, Ptotal, Pdate, other, Pid};
        try {
            CRUD.executeUpdate(sql);
            return true; // 更新成功
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // 更新失败
        }
    }

    // 删除采购明细记录
    public boolean deletePayMain(String attribute, String value){
        String sql;
        if ("Pid".equalsIgnoreCase(attribute)) {
            //如果是Pid的的话，直接查询
            sql = "DELETE FROM tb_pay_main WHERE Pid = ?";
        } else {
            // 如果是其他的需要添加属性查询
            sql = "SELECT * FROM tb_pay_main WHERE " + attribute + " = ?";
        }

        try {
            PreparedStatement pstmt = CRUD.getConnection().prepareStatement(sql);
            pstmt.setString(1, value);

            if ("PDid".equalsIgnoreCase(attribute)) {
                int rowsAffected = pstmt.executeUpdate();
                return rowsAffected > 0;
            } else {
                //sql查询出来的行
                ResultSet rs = pstmt.executeQuery();
                int rowCount = 0;
                while (rs.next()) {
                    rowCount++;
                    int Pid = Integer.parseInt(rs.getString("Pid"));
                    int Eid = Integer.parseInt(rs.getString("Eid"));
                    System.out.println("找到采购主表内容为：Pid=" + Pid + ", Eid" + Eid);
                }

                if (rowCount == 0) {
                    System.out.println("没有找到 " + attribute + " = " + value + " 的采购主表内容");
                    return false;
                } else if (rowCount == 1) {

                    int rowsAffected = pstmt.executeUpdate();
                    return rowsAffected > 0;
                } else {
                    System.out.println("找到多个符合条件的采购主表内容 " + attribute + " = " + value);
                    return false;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Delete failed
        }
    }

    // 查询采购明细信息
    public DefaultTableModel queryPayMain() {
        String sql = "SELECT * FROM tb_pay_main";
        try {
            return CRUD.executeQueryAndBuildTableModel(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return null; // 查询失败
        }
    }
}
